package houtai.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface DashBoardMapper {
    @Select("select count(mobile) from tb_user")
    Integer queryCumulativeUsers();// 累计用户

    @Select("select sum(num_active) from (select num_active from tb_analysis_by_day order by record_date desc limit 30) as na")
    Integer queryActivePassMonth();// 过去30天活跃用户

    @Select("select sum(num_active)\n" +
            "        from (select num_active from tb_analysis_by_day order by record_date desc limit 7) na")
    Integer queryActivePassWeek();// 过去7天活跃用户

    @Select("select num_registered\n" +
            "        from tb_analysis_by_day\n" +
            "        order by record_date desc\n" +
            "        limit 1")
    Integer queryNewUsersToday();// 今日新增用户

    @Select("select num_login\n" +
            "        from tb_analysis_by_day\n" +
            "        order by record_date desc\n" +
            "        limit 1")
    Integer queryLoginTimesToday();// 今日登录次数

    @Select("select num_active\n" +
            "        from tb_analysis_by_day\n" +
            "        order by record_date desc\n" +
            "        limit 1")
    Integer queryActiveUsersToday();// 今日活跃用户

    @Select("select if(\n" +
            "                       (((select num_registered\n" +
            "                          from tb_analysis_by_day\n" +
            "                          order by record_date desc\n" +
            "                          limit 1) - (select num_registered\n" +
            "                                      from tb_analysis_by_day\n" +
            "                                      where record_date\n" +
            "                                                <\n" +
            "                                            (select record_date\n" +
            "                                             from tb_analysis_by_day\n" +
            "                                             order by record_date desc\n" +
            "                                             limit 1)\n" +
            "                                      order by record_date desc\n" +
            "                                      limit 1))\n" +
            "                           <\n" +
            "                        0),\n" +
            "                       (select concat('-', 100 - (((select num_registered\n" +
            "                                                    from tb_analysis_by_day\n" +
            "                                                    order by record_date desc\n" +
            "                                                    limit 1) / (select num_registered\n" +
            "                                                                from tb_analysis_by_day\n" +
            "                                                                where record_date\n" +
            "                                                                          <\n" +
            "                                                                      (select record_date\n" +
            "                                                                       from tb_analysis_by_day\n" +
            "                                                                       order by record_date desc\n" +
            "                                                                       limit 1)\n" +
            "                                                                order by record_date desc\n" +
            "                                                                limit 1)) * 100))),\n" +
            "                       (select concat((((select num_registered\n" +
            "                                         from tb_analysis_by_day\n" +
            "                                         order by record_date desc\n" +
            "                                         limit 1) / (select num_registered\n" +
            "                                                     from tb_analysis_by_day\n" +
            "                                                     where record_date\n" +
            "                                                               <\n" +
            "                                                           (select record_date\n" +
            "                                                            from tb_analysis_by_day\n" +
            "                                                            order by record_date desc\n" +
            "                                                            limit 1)\n" +
            "                                                     order by record_date desc\n" +
            "                                                     limit 1)) * 100) - 100)))")
    Integer queryNewUsersTodayRate();// 今日新增用户涨跌率，单位百分数，正数为涨，负数为跌

    @Select("select if(\n" +
            "                       (((select num_login\n" +
            "                          from tb_analysis_by_day\n" +
            "                          order by record_date desc\n" +
            "                          limit 1) - (select num_login\n" +
            "                                      from tb_analysis_by_day\n" +
            "                                      where record_date <\n" +
            "                                            (select record_date\n" +
            "                                             from tb_analysis_by_day\n" +
            "                                             order by record_date desc\n" +
            "                                             limit 1)\n" +
            "                                      order by record_date desc\n" +
            "                                      limit 1)) < 0),\n" +
            "                       (select concat('-', 100 - (((select num_login\n" +
            "                                                    from tb_analysis_by_day\n" +
            "                                                    order by record_date desc\n" +
            "                                                    limit 1) / (select num_login\n" +
            "                                                                from tb_analysis_by_day\n" +
            "                                                                where record_date < (select record_date\n" +
            "                                                                                        from tb_analysis_by_day\n" +
            "                                                                                        order by record_date desc\n" +
            "                                                                                        limit 1)\n" +
            "                                                                order by record_date desc\n" +
            "                                                                limit 1)) * 100))),\n" +
            "                       (select concat((((select num_login\n" +
            "                                         from tb_analysis_by_day\n" +
            "                                         order by record_date desc\n" +
            "                                         limit 1) / (select num_login\n" +
            "                                                     from tb_analysis_by_day\n" +
            "                                                     where record_date < (select record_date\n" +
            "                                                                             from tb_analysis_by_day\n" +
            "                                                                             order by record_date desc\n" +
            "                                                                             limit 1)\n" +
            "                                                     order by record_date desc\n" +
            "                                                     limit 1)) * 100) - 100)))")
    Integer queryLoginTimesTodayRate();// 今日登录次数涨跌率，单位百分数，正数为涨，负数为跌

    @Select("select if(\n" +
            "                       (((select num_active\n" +
            "                          from tb_analysis_by_day\n" +
            "                          order by record_date desc\n" +
            "                          limit 1) - (select num_active\n" +
            "                                      from tb_analysis_by_day\n" +
            "                                      where record_date <\n" +
            "                                            (select record_date\n" +
            "                                             from tb_analysis_by_day\n" +
            "                                             order by record_date desc\n" +
            "                                             limit 1)\n" +
            "                                      order by record_date desc\n" +
            "                                      limit 1)) < 0),\n" +
            "                       (select concat('-', 100 - (((select num_active\n" +
            "                                                    from tb_analysis_by_day\n" +
            "                                                    order by record_date desc\n" +
            "                                                    limit 1) / (select num_active\n" +
            "                                                                from tb_analysis_by_day\n" +
            "                                                                where record_date < (select record_date\n" +
            "                                                                                        from tb_analysis_by_day\n" +
            "                                                                                        order by record_date desc\n" +
            "                                                                                        limit 1)\n" +
            "                                                                order by record_date desc\n" +
            "                                                                limit 1)) * 100))),\n" +
            "                       (select concat((((select num_active\n" +
            "                                         from tb_analysis_by_day\n" +
            "                                         order by record_date desc\n" +
            "                                         limit 1) / (select num_active\n" +
            "                                                     from tb_analysis_by_day\n" +
            "                                                     where record_date < (select record_date\n" +
            "                                                                             from tb_analysis_by_day\n" +
            "                                                                             order by record_date desc\n" +
            "                                                                             limit 1)\n" +
            "                                                     order by record_date desc\n" +
            "                                                     limit 1)) * 100) - 100)))")
    Integer queryActiveUsersTodayRate();// 今日活跃用户涨跌率，单位百分数，正数为涨，负数为跌
}